package si.sp.socialnetwork;

import java.sql.*;


public class DatabaseDAO 
{
	private ResultSet results; 
	Connection con;
	Statement ps;
	String query;

	public DatabaseDAO() throws SQLException
	{
		try    {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/druzabno_omrezje_db","root","admin");
		}
		catch(Exception ex) {
			System.out.println("Exception Occur :" + ex);
		}
	}

	public ResultSet getRacun()
	{
		try {
			ps = con.createStatement();
			query = "SELECT * FROM druzabno_omrezje_db.racun";
			results = ps.executeQuery( query );
		} 
		catch(Exception ex) {
			results = null;
			System.out.println("Exception Occur :" + ex);
		}

		return results;
	}
	
	public ResultSet getOseba()
	{
		try {
			ps = con.createStatement();
			query = "SELECT * FROM druzabno_omrezje_db.oseba";
			results = ps.executeQuery( query );
		} 
		catch(Exception ex) {
			results = null;
			System.out.println("Exception Occur :" + ex);
		}

		return results;
	}
	
	public ResultSet getUser()
	{
		try {
			ps = con.createStatement();
			query = "SELECT IDracun, email, geslo, ime, priimek, kraj, spol, rojstni_datum FROM (druzabno_omrezje_db.racun INNER JOIN druzabno_omrezje_db.oseba ON racun.IDracun = oseba.Racun_IDracun)";
			results = ps.executeQuery( query );
		} 
		catch(Exception ex) {
			results = null;
			System.out.println("Exception Occur :" + ex);
		}

		return results;
	}
        
        public ResultSet getMatchingUser(String name)
	{
		try {
			ps = con.createStatement();
			query = "SELECT IDracun, email, geslo, ime, priimek, kraj, spol, rojstni_datum \r\n" + 
                                "FROM (druzabno_omrezje_db.racun INNER JOIN druzabno_omrezje_db.oseba ON racun.IDracun = oseba.Racun_IDracun) \r\n" + 
                                "WHERE ime =" + name;
			results = ps.executeQuery( query );
		} 
		catch(Exception ex) {
			results = null;
			System.out.println("Exception Occur :" + ex);
		}

		return results;
	}

	public void insertUser( User user )
	{
		try {
			ps = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );
			query = "SELECT * FROM druzabno_omrezje_db.racun";
			results = ps.executeQuery( query );

			results.moveToInsertRow();
			results.updateString("email", user.getEmail());
			results.updateString("geslo", user.getGeslo());
			results.insertRow();
			

			ps = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );
			query = "SELECT * FROM druzabno_omrezje_db.oseba";
			results = ps.executeQuery( query );

			results.moveToInsertRow();
			results.updateString("ime", user.getIme());
			results.updateString("priimek", user.getPriimek());
			results.updateString("kraj", user.getKraj());
			results.updateString("spol", user.getSpol());
			results.updateDate("rojstni_datum", user.getRd());
			results.insertRow();
		} 
		catch(Exception ex) {
			results = null;
			System.out.println("Exception Occur :" + ex);
		}
	}    

	
	public ResultSet getKrogiForUserID(int id)
	{
		try {
			ps = con.createStatement();
			query = "SELECT IDracun, email, ime, priimek, kraj, spol, rojstni_datum, IDkrog, ime_kroga \r\n" + 
					"FROM (druzabno_omrezje_db.racun INNER JOIN druzabno_omrezje_db.oseba INNER JOIN druzabno_omrezje_db.krog \r\n" + 
					"ON oseba.IDoseba = krog.Oseba_IDoseba && racun.IDracun = krog.Oseba_Racun_IDracun)\r\n" + 
					"WHERE Oseba_Racun_IDracun = " + id;
			results = ps.executeQuery( query );
		} 
		catch(Exception ex) {
			results = null;
			System.out.println("Exception Occur :" + ex);
		}

		return results;
	}
	
	public ResultSet getSlike(int id){
            try {
			ps = con.createStatement();
			query = "SELECT ime, priimek, url, opis, ime_kroga \r\n" + 
					"FROM (druzabno_omrezje_db.vidnostslike INNER JOIN druzabno_omrezje_db.slika \r\n" +
                                        "INNER JOIN druzabno_omrezje_db.oseba INNER JOIN druzabno_omrezje_db.krog \r\n" + 
					"ON vidnostslike.Slika_IDslika = slika.IDslika && slika.Oseba_Racun_IDracun = oseba.Racun_IDracun && vidnostslike.krog_id = krog.IDkrog)\r\n" + 
					"WHERE Oseba_Racun_IDracun = " + id;
			results = ps.executeQuery( query );
		} 
		catch(Exception ex) {
			results = null;
			System.out.println("Exception Occur :" + ex);
		}

		return results;
        }
        
        public ResultSet getObjave(int id){
            try {
			ps = con.createStatement();
			query = "SELECT ime, priimek, spletna_stran, vsebina, ime_kroga \r\n" + 
					"FROM (druzabno_omrezje_db.vidnostobjave INNER JOIN druzabno_omrezje_db.objava \r\n" +
                                        "INNER JOIN druzabno_omrezje_db.oseba INNER JOIN druzabno_omrezje_db.krog \r\n" + 
					"ON vidnostobjave.Objava_IDobjava = objava.IDobjava && objava.Oseba_Racun_IDracun = oseba.Racun_IDracun && vidnostobjave.krog_id = krog.IDkrog)\r\n" + 
					"WHERE Oseba_Racun_IDracun = " + id;
			results = ps.executeQuery( query );
		} 
		catch(Exception ex) {
			results = null;
			System.out.println("Exception Occur :" + ex);
		}

		return results;
        }
        
        public ResultSet getMatchingUsers(String un, String psw)
	{
		try {
			ps = con.createStatement();
			query = "SELECT * FROM druzabno_omrezje_db.racun WHERE email='"+un+"' AND geslo='"+psw+"' ";
			results = ps.executeQuery( query );
		} 
		catch(Exception ex) {
			results = null;
			System.out.println("Exception Occur :" + ex);
		}

		return results;
	}
	
	public void closeConnection()
	{
		try   {
			if (ps != null) {
				ps.close();
			}
		} catch(Exception ex) {
			System.out.println("Error closing connection :" + ex);
		}
	}
}
